﻿using Hsp.Auto.Interface;
using Hsp.Auto.Model.Models;
using System;
using System.Collections.Generic;
using System.Text;

namespace Hsp.Auto.SqlServer
{
    public class TableService : SqlServerHelper, ITableService
    {
        //private static string connectionString = string.Empty;

        //static TableService()
        //{
        //	connectionString = DataHelper.connectionString;
        //}

        #region 表列表信息

        /// <summary>
        /// 表列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> TableList<T>()
        {
            return TableList<T>("", "");
        }

        /// <summary>
        /// 表列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> TableList<T>(string dbName, string tableNames)
        {
            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (o.name IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
			SELECT o.[id] AS Id, o.[name] AS Name, ISNULL(ep.[value], o.[name]) AS Comment
			--, *
			FROM syscolumns AS c
			INNER JOIN sysobjects AS o ON c.[id] = o.[id] AND (o.[xtype] = 'U') --AND (o.[name] <> 'dtproperties')
			LEFT JOIN sys.extended_properties ep ON o.[id] = ep.major_id AND ep.minor_id = 0
			WHERE (c.colorder = 1) AND (o.category = 0) {0} ORDER BY c.[id];", strWhere);
            return ListQuery<T>(strSql);
        }

        #endregion

        #region 表字段列表信息

        /// <summary>
        /// 表字段列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> ColumnList<T>()
        {
            return ColumnList<T>("");
        }

        /// <summary>
        /// 表字段列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="tableNames">库表名称集合，逗号隔开</param>
        /// <returns></returns>
        public List<T> ColumnList<T>(string tableNames)
        {
            return this.ColumnList<T>("", tableNames);
        }

        /// <summary>
        /// 表字段列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="dbName">库名称</param>
        /// <param name="tableNames">库表名称集合，逗号隔开</param>
        /// <returns></returns>
        public List<T> ColumnList<T>(string dbName, string tableNames)
        {
            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (o.name IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
            ;WITH fkTb AS (
	            SELECT fk.name, fk.object_id, fk.parent_object_id, fk.referenced_object_id
	            , fkc.parent_column_id, fkc.referenced_column_id
	            FROM sys.foreign_keys fk
	            INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id 
            )
            SELECT c.colid AS Id, c.name AS Name, sc.DATA_TYPE AS DataType
                , CASE WHEN CHARINDEX('char', sc.DATA_TYPE ) > 0 THEN CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN sc.DATA_TYPE + '('+ CAST( CHARACTER_MAXIMUM_LENGTH AS varchar) + ')' ELSE sc.DATA_TYPE + '(MAX)' END ELSE sc.DATA_TYPE END AS ColumnType
	            , c.isnullable AS Nullable
	            , CONVERT(NVARCHAR(255), ISNULL(p.value, c.name)) AS Comment
	            , CASE WHEN CHARINDEX('image', sc.DATA_TYPE) > 0 OR CHARINDEX('binary', sc.DATA_TYPE) > 0 THEN 0 
	             WHEN CHARINDEX('xml', sc.DATA_TYPE) > 0 THEN 0 ELSE 1	END AS Queryable
	            , CASE WHEN ISNULL(sc.NUMERIC_PRECISION, 0) > 0 THEN sc.NUMERIC_PRECISION WHEN ISNULL(sc.CHARACTER_MAXIMUM_LENGTH, 0) > 2147483647 THEN 2147483647 ELSE ISNULL(sc.CHARACTER_MAXIMUM_LENGTH, 0) END AS [LENGTH]
	            , ISNULL(sc.NUMERIC_SCALE, 0) AS SCALE
	            , ISNULL(sc.[COLUMN_DEFAULT],'') AS DefaultValue
	            , IsPK = CASE WHEN EXISTS ( SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name IN (
	            SELECT name FROM sysindexes WHERE indid IN ( SELECT indid FROM sysindexkeys WHERE id = c.id AND colid = c.colid ) ) )
	            THEN '1' ELSE '0' END
	            , COLUMNPROPERTY(c.[id], c.[name], 'IsIdentity') AS IsIdentity
	            , o.name AS TableName
	            , ISNULL(ro.name, '') AS Referenced_Table_Name, ISNULL(rc.name, '') AS Referenced_Column_Name
            FROM sysobjects o
	            LEFT JOIN syscolumns c ON o.id = c.id
	            LEFT JOIN information_schema.COLUMNS sc ON sc.TABLE_NAME = o.[name] AND sc.COLUMN_NAME = c.[name]
	            LEFT JOIN sys.extended_properties p ON p.major_id = c.id AND p.minor_id = c.colid AND p.name = 'MS_Description'
	            LEFT OUTER JOIN fkTb AS fk ON fk.parent_object_id = o.id AND fk.parent_column_id = c.colid
	            LEFT OUTER JOIN sys.objects ro ON ro.object_id = fk.referenced_object_id
	            LEFT OUTER JOIN sys.columns rc ON rc.object_id = ro.object_id AND rc.column_id = fk.referenced_column_id
			WHERE (o.type = 'U') {0} ORDER BY c.[id], c.colorder;", strWhere);
            return ListQuery<T>(strSql);
        }

        #endregion

        #region 表被引用数据信息

        /// <summary>
        /// 表被引用数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> ReferenceList<T>()
        {
            return this.ReferenceList<T>("");
        }

        /// <summary>
        /// 表被引用数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="tableNames">库表名称集合，逗号隔开</param>
        /// <returns></returns>
        public List<T> ReferenceList<T>(string tableNames)
        {
            return this.ReferenceList<T>("", tableNames);
        }

        /// <summary>
        /// 表被引用数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> ReferenceList<T>(string dbName, string tableNames)
        {
            var strWhere = string.Empty;

            //if (!string.IsNullOrEmpty(tableNames))
            //{
            //    tableNames = tableNames.Trim().Replace(",", "','");
            //    strWhere = string.Format(@" AND (c.table_name IN ('{0}'))", tableNames);
            //}

            var strSql = string.Format(@"
            ;WITH fkTb AS (
	            SELECT fk.name, fk.object_id, fk.parent_object_id, fk.referenced_object_id
	            , fkc.parent_column_id, fkc.referenced_column_id
	            FROM sys.foreign_keys fk
	            INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id 
            )
            SELECT 
            fk.object_id AS Id, rc.name AS Name, ro.name AS TableName
            , o.name AS Primarykey_Table_Name, c.name AS Primarykey_Column_Name
            --, c.column_id, c.object_id, fk.*
            FROM sys.objects o
            INNER JOIN sys.columns c ON c.object_id = o.object_id
            INNER JOIN fkTb AS fk ON fk.parent_object_id = o.object_id AND fk.parent_column_id = c.column_id
            INNER JOIN sys.objects ro ON ro.object_id = fk.referenced_object_id
            INNER JOIN sys.columns rc ON rc.object_id = ro.object_id AND rc.column_id = fk.referenced_column_id
            ORDER BY fk.object_id;
			", dbName, strWhere);

            return ListQuery<T>(strSql);
        }

        #endregion

        #region 表注释中可添加枚举数据信息

        /// <summary>
        /// 表注释中可添加枚举数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> EnumList<T>()
        {
            return this.EnumList<T>("");
        }

        /// <summary>
        /// 表注释中可添加枚举数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public List<T> EnumList<T>(string dbName)
        {
            var strSql = string.Format(@"
            SELECT DISTINCT c.name AS Name, CONVERT(NVARCHAR(255), p.value) AS Comment
            FROM sys.objects o
            INNER JOIN sys.columns c ON c.object_id = o.object_id
            INNER JOIN information_schema.COLUMNS sc ON sc.TABLE_NAME = o.[name] AND sc.COLUMN_NAME = c.[name]
            INNER JOIN sys.extended_properties p ON p.major_id = c.object_id AND p.minor_id = c.column_id AND p.name = 'MS_Description'
            WHERE (CHARINDEX(':', CONVERT(NVARCHAR(255), p.value)) > 0 OR CHARINDEX('：', CONVERT(NVARCHAR(255), p.value)) > 0)
            AND (sc.DATA_TYPE = 'int') ORDER BY CONVERT(NVARCHAR(255), p.value);
			");

            return ListQuery<T>(strSql);
        }

        /// <summary>
        /// 获取当前数据库名称
        /// </summary>
        /// <returns></returns>
        public string CurrentDbName()
        {
            var result = ExecuteScalar(@"Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid)");
            return result == null ? "" : result.ToString();
        }

        #endregion

        #region 表主键列应用信息

        /// <summary>
        /// 表主键列应用信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> KeyColumnList<T>()
        {
            return KeyColumnList<T>("");
        }

        /// <summary>
        /// 表主键列应用信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="dbName">数据库名称</param>
        /// <returns></returns>
        public List<T> KeyColumnList<T>(string tableNames)
        {
            return KeyColumnList<T>("", tableNames);
        }

        public List<T> KeyColumnList<T>(string dbName, string tableNames)
        {
            return new List<T>();
        }

        #endregion

        #region 表索引数据信息

        /// <summary>
        /// 表索引数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> IndexList<T>()
        {
            return IndexList<T>("");
        }

        /// <summary>
        /// 表索引数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="tableNames">表名称集合</param>
        /// <returns></returns>
        public List<T> IndexList<T>(string tableNames)
        {
            return IndexList<T>("", tableNames);
        }

        public List<T> IndexList<T>(string dbName, string tableNames)
        {
            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (c.name IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
                SELECT c.name AS TableName, a.name AS IndexName, d.isnullable AS NonUnique
                , ( SELECT STUFF((SELECT ',' + c.name
                 FROM sysindexkeys i
                 INNER JOIN syscolumns c ON c.id = i.id AND c.colid = i.colid
                 WHERE (i.id = b.id) AND (i.indid = a.indid)
                  order by i.colid FOR XML PATH('')
                ),1,1,'')) AS IndexColumns
                FROM sysindexes a
                INNER JOIN sysindexkeys b ON a.id = b.id AND a.indid = b.indid
                INNER JOIN sysobjects c ON b.id = c.id
                INNER JOIN syscolumns d ON b.id = d.id AND b.colid = d.colid
                WHERE (a.indid NOT IN ( 0, 255)) AND (c.xtype='U')
                {0} --查指定表  
                GROUP BY c.name, a.name, a.indid, b.id, d.isnullable
                ORDER BY c.name, a.indid;
			", strWhere);

            return ListQuery<T>(strSql);
        }

        #endregion



        public List<Columns> SqliteColumnList<T>(string tableNames)
        {
            throw new NotImplementedException();
        }

        public List<T> ViewList<T>(string dbName)
        {
            return null;
        }

        public List<T> ViewColumnList<T>(string dbName)
        {
            return null;
        }
    }
}
